﻿/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
			   SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/
GO

/*  Change the db_owner to sa											*/
EXEC sp_changedbowner 'sa'
GO

/*  Upgrade version 1 to version 2										*/
:r .\Cleanup\upgrade_v1.sql

/*  Store/Retrieve Configuration Values									*/
EXEC	[dbo].[spPostDeploy_LookupTable_MsxConfiguration]

/*  Store/Retrieve Configuration Values									*/
EXEC	[dbo].[spPostDeploy_LookupTable_MsxConfigurationEnvironment]

/*  Prepopulate the SQL Server support dates from lifecycle				*/
EXEC	[dbo].[spPostDeploy_LookupTable_SqlSupport]

/*  Prepopulate the list of SQL Server Versions							*/
EXEC	[dbo].[spPostDeploy_LookupTable_SqlVersions]

/*  Setup the Default MSX Server Groups									*/
EXEC	[dbo].[spPostDeploy_LookupTable_systargetservergroups]

/*  Set database settings												*/
EXEC	[dbo].[spMaint_InitDatabase]

/*  Configure msdb with indexes, roles, and permissions					*/
EXEC	[dbo].[spMaint_InitMsdb]

/*  Configure default alerts and operators								*/
EXEC	[dbo].[spMaint_InitAlertsOperators]
GO

/*  Add the SQL Service Account Domain Group to the MemberServer Role	*/
EXECUTE sp_addrolemember @rolename = N'MemberServer', @membername = N'$(SqlServiceAccountDomainGroup)';
GO

/*  Configure the MSX Server											*/
USE [master]
GO

DECLARE @domain_account sysname
IF LTRIM(RTRIM('$(SqlServiceAccountDomainGroup)')) NOT IN ('', 'None', 'your_domain.com\your_group_name')
	SET @domain_account = LTRIM(RTRIM('$(SqlServiceAccountDomainGroup)'))
ELSE
	SET @domain_account = NULL


IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @domain_account) AND (@domain_account IS NOT NULL)
	EXEC ('CREATE LOGIN [' + @domain_account + '] FROM WINDOWS')

USE msdb

IF NOT EXISTS (SELECT name FROM [msdb].[dbo].sysoperators WHERE name = N'MSXOperator')
	EXEC [msdb].[dbo].[sp_add_operator]	@name                       =  N'MSXOperator', 
										@enabled                    =  1, 
										@weekday_pager_start_time   =  90000, 
										@weekday_pager_end_time     =  180000, 
										@saturday_pager_start_time  =  90000, 
										@saturday_pager_end_time    =  180000, 
										@sunday_pager_start_time    =  90000, 
										@sunday_pager_end_time      =  180000, 
										@pager_days                 =  0, 
										@email_address              =  N'$(AdminEmailAccount)@$(AdminEmailDomainName)', 
										@category_name              =  N'[Uncategorized]'

IF @domain_account IS NOT NULL
  BEGIN
	IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @domain_account)
		EXEC ('CREATE USER [$(SqlServiceAccountDomainGroup)] FOR LOGIN [' + @domain_account + ']')

	EXEC sp_addrolemember N'TargetServersRole', @domain_account
  END
GO


/*  Create the local jobs									*/
/*	:r .\Local_Jobs\JOB-Create_Local_Jobs.sql				*/
GO
